[Mysql] 测试mysql or和in对sql查询效率的影响

1、创建基表测试数据

CREATE TABLE `test1` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4;

insert into test1(name,age) values('lucy',10),('bobo',18),('david',20),('tom',21),('dobu',22),('dali',12);

2、创建中间表

 CREATE TABLE `testfororder` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14699990 DEFAULT CHARSET=utf8mb4;

创建存储过程对中间表插入数据：

drop procedure if exists test;
delimiter //
create procedure test()
 begin
  declare i int;
  set i=0;
  while i<300000 do
   insert into testfororder(newid,name,age) select concat(i,name),FLOOR(18 + (RAND() * 12)) from test1;
   set i=i+1;
  end while;
 end//
 delimiter ;
调用存储过程：
call test();

3、创建测试表：

CREATE TABLE `sequence` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `newid` int(10) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=89179437 DEFAULT CHARSET=utf8mb4;

insert into sequence(newid,name,age) select * from testfororder;

数据量根据自己需求可以多次导入

4、查看sequence测试表数据：

select count(*) from sequence;
+----------+
| count(*) |
+----------+
| 12600000 |
+----------+
1 row in set (2.83 sec)

5、在newid没有索引的情况下，in和or的对比：

repl@db 16:14:  [aaaa]> select * from sequence where  newid in (116670,116677,116684,116691,116698,116705,116719,116726);
+----------+--------+-----------+------+
| id       | newid  | name      | age  |
+----------+--------+-----------+------+
|   100003 | 116670 | 2381lucy  |   26 |
...
| 76679494 | 116726 | 2382bobo  |   21 |
+----------+--------+-----------+------+
56 rows in set (4.47 sec)

select * from sequence where  newid =116670 or newid = 116677 or newid = 116684 or newid =116691 or newid = 116698 or newid=116705 or newid=116719 or newid=116726;
+----------+--------+-----------+------+
| id       | newid  | name      | age  |
+----------+--------+-----------+------+
|   100003 | 116670 | 2381lucy  |   26 |
...
| 76679494 | 116726 | 2382bobo  |   21 |
+----------+--------+-----------+------+
56 rows in set (6.77 sec)

没有索引的情况下使用in查询效率高于or

6、创建newid索引，再次进行测试：

 create index newid_ind on sequence(newid);
select * from sequence where  newid in (116670,116677,116684,116691,116698,116705,116719,116726);
+----------+--------+-----------+------+
| id       | newid  | name      | age  |
+----------+--------+-----------+------+
|   100003 | 116670 | 2381lucy  |   26 |
...
| 76679494 | 116726 | 2382bobo  |   21 |
+----------+--------+-----------+------+
56 rows in set (0.00 sec)

select * from sequence where  newid =116670 or newid = 116677 or newid = 116684 or newid =116691 or newid = 116698 or newid=116705 or newid=116719 or newid=116726;
+----------+--------+-----------+------+
| id       | newid  | name      | age  |
+----------+--------+-----------+------+
|   100003 | 116670 | 2381lucy  |   26 |
...
| 76679494 | 116726 | 2382bobo  |   21 |
+----------+--------+-----------+------+
56 rows in set (0.01 sec)

在newid存在索引的情况下，使用or和in查询对sql查询效率影响较小。